# -*- coding: utf-8 -*-

from robot.utils import ConnectionCache
from robot.api.deco import keyword
from robot.api import logger
import importlib

from .UtilLib import UtilLib


class DatabaseLib(object):

    def __init__(self):
        self._connections = ConnectionCache()
        self.db_api_module_name = None

        self.u = UtilLib()

    @keyword('Connect To Database')
    def connect_to_database(self, dbapiModuleName=None, db_connect_string='', aliasPre=None):
        """
        | Connect To Database Using Custom Params | dbConnStr | employee|
        """
        db_api_2 = importlib.import_module(dbapiModuleName)
        db_connect = 'db_api_2.connect(%s)' % db_connect_string
        self.db_api_module_name = dbapiModuleName
        alias = self._get_alias_name(aliasPre)

        conn = self._connections.register(eval(db_connect), alias)
        logger.info("[MYSQL建立连接] 位置: {0}, 别名: {1}, 地址: {2}".format(conn, alias, db_connect_string))

        return conn

    @keyword('Close Database')
    def close_database(self, aliasPre=None):
        """
         | Close Database Connection | employee|
        """
        alias = self._get_alias_name(aliasPre)
        conn = self._connections.get_connection(alias_or_index=alias)
        conn.close()
        logger.info('[MYSQL关闭连接] 数据库名称: {0}'.format(alias))

    @keyword('Execute Sql String')
    def execute_sql_string(self, aliasPre, sqlString, opType="insert"):
        """
         | Execute Sql String | employee | DELETE FROM person_employee_table; DELETE FROM person_table |
        """
        alias = self._get_alias_name(aliasPre)
        logger.info("[MYSQL执行{0}语句] 别名: {1}, 执行sql: {2}".format(opType, alias, sqlString))
        conn = self._connections.get_connection(alias)
        cur = conn.cursor()
        result = cur.execute(sqlString)
        conn.commit()

        return result

    @keyword('Row Count Is Equal To X')
    def row_count_is_equal_to_x(self, aliasPre, sqlString, numRows):
        """
        | Row Count Is Equal To X | SELECT id FROM person WHERE first_name = 'John' | 0 |
        """
        alias = self._get_alias_name(aliasPre)
        logger.info("[MYSQL查询行数] 别名: {0}, 期望返回{1}行, 查询sql: {2} ".format(alias, numRows, sqlString))
        conn = self._connections.get_connection(alias_or_index=alias)
        cur = conn.cursor()
        cur.execute(sqlString)
        rowCount = cur.rowcount
        if rowCount != int(numRows.encode('ascii')):
            raise AssertionError("[MYSQL查询行数] 别名: {0}, 查询sql返回的结果行数为: {1}行, 不符合预期结果".format(alias, rowCount))
        logger.info("[MYSQL查询行数] 别名: {0}, 查询sql返回的结果行数为: {1}行, 符合预期结果".format(alias, rowCount))

    @keyword('Query')
    def query(self, aliasPre, sqlString):
        alias = self._get_alias_name(aliasPre)
        conn = self._connections.get_connection(alias_or_index=alias)
        # 查询sql
        cur = conn.cursor()
        cur.execute(sqlString)
        allRows = cur.fetchall()

        mappedRows = []
        col_names = [c[0] for c in cur.description]
        for rowIdx in range(len(allRows)):
            d = {}
            for colIdx in range(len(allRows[rowIdx])):
                d[col_names[colIdx]] = allRows[rowIdx][colIdx]
            mappedRows.append(d)

        return mappedRows
    
    def _get_alias_name(self, aliasPre):

        return aliasPre + "_" + self.u.get_venv()